package com.uu.it.tracker.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class DBAdapter {
	private static final String DATABASE_NAME = "tracker.db";
	private static final String DATABASE_TABLE = "history";
	private static final int DATABASE_VERSION = 1;
	
	//Index key
	public static final String KEY_ID = "_id";
	//set column and column index
	public static final String KEY_DATE = "date";
	public static final int DATE_COLUMN = 1;
	public static final String KEY_START_TIME = "starttime";
	public static final int STARTTIME_COLUMN = 2;
	public static final String KEY_END_TIME = "endtime";
	public static final int ENDTIME_COLUMN = 3;
	public static final String KEY_DISTANCE = "distance";
	public static final int DISTANCE_COLUMN = 4;
	public static final String KEY_AVG_SPEED = "avg_speed";
	public static final int AVG_SPEED_COLUMN = 5;
	public static final String KEY_MAX_SPEED = "max_speed";
	public static final int MAX_SPEED_COLUMN = 6;
	
	//create a new database
	private static final String DATABASE_CREATE = "create table " + DATABASE_TABLE +
	" (" + KEY_ID + " integer primary key autoincrement, " + KEY_DATE + " text not null, "
	+ KEY_START_TIME + " text not null, " + KEY_END_TIME + " text not null, " + 
	KEY_DISTANCE +	" text not null, " + KEY_AVG_SPEED + " text not null, " + 
	KEY_MAX_SPEED + " text not null);";
	
	//database instance
	private SQLiteDatabase db;
	
	private final Context context;
	//database open/upgrade helper
	private DBHelper dbHelper;
	
	
	public DBAdapter (Context _context) {
		context = _context;
		dbHelper = new DBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public void open() throws SQLException {
		try {
			db = dbHelper.getWritableDatabase();
		}catch(SQLException ex) {
			db = dbHelper.getReadableDatabase();
			throw new SQLException("can't open database");
		}
		//return this;
	}
	
	public void close() {
		db.close();
	}
	
	public long insertHistory(String date, String starttime, String endtime, String distance, String avg_speed, String max_speed) {
		ContentValues contentvalues = new ContentValues();
		contentvalues.put(KEY_DATE, date);
		contentvalues.put(KEY_START_TIME, starttime);
		contentvalues.put(KEY_END_TIME, endtime);
		contentvalues.put(KEY_DISTANCE, distance);
		contentvalues.put(KEY_AVG_SPEED, avg_speed);
		contentvalues.put(KEY_MAX_SPEED, max_speed);
		return db.insert(DATABASE_TABLE, null, contentvalues);
	}
	
	public boolean deleteHistory(String _rowIndex) {
		return db.delete(DATABASE_TABLE, KEY_ID + "=" + _rowIndex, null)>0;
	}
	
	public Cursor getAllHistory() {
		return db.query(DATABASE_TABLE, new String[] {
			KEY_ID, KEY_DATE, KEY_START_TIME, KEY_END_TIME , KEY_DISTANCE, KEY_AVG_SPEED, KEY_MAX_SPEED
		}, null, null, null, null, null);
	}
	
	public Cursor getHistory (long _rowIndex) {
		Cursor mCursor = 
			db.query(true, DATABASE_TABLE, new String[] {
					KEY_ID, KEY_DATE, KEY_START_TIME, KEY_END_TIME, KEY_DISTANCE, KEY_AVG_SPEED, KEY_MAX_SPEED
			}, KEY_ID+"="+_rowIndex, null, null, null, null, null);
		
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor getHistory(String _s){
		Cursor mCursor =
			db.query(true, DATABASE_TABLE, new String[] {KEY_ID, KEY_DATE, KEY_START_TIME, KEY_END_TIME, KEY_DISTANCE, KEY_AVG_SPEED, KEY_MAX_SPEED
					}, 
					_s, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public boolean updateHistory(long _rowIndex, String _date, String _starttime, String _endtime, String _distance, String _avg_speed, String _max_speed) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_DATE, _date);
		contentValues.put(KEY_START_TIME, _starttime);
		contentValues.put(KEY_END_TIME, _endtime);
		contentValues.put(KEY_DISTANCE, _distance);
		contentValues.put(KEY_AVG_SPEED, _avg_speed);
		contentValues.put(KEY_MAX_SPEED, _max_speed);
		
		return db.update(DATABASE_TABLE, contentValues, KEY_ID+"="+_rowIndex, null)>0;
	}
	
	private static class DBHelper extends SQLiteOpenHelper {

		public DBHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(DATABASE_CREATE);	
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			Log.w("DBAdapter", "Upgrading database from version "+ oldVersion
					+ " to " + newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);	
			onCreate(db);
		}
		
	}
	
}
